﻿using Newtonsoft.Json;
using ProjBackEnd.Common;
using ProjBackEnd.Common.Helper;
using ProjBackEnd.Models;
using ProjBackEnd.Models.AppModel.AppApproveModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ProjBackEnd.Service
{
    public sealed partial class AppApproveService
    {
        /// <summary>
        /// 获取企业及待审批数据
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public string GetCompanyWaitYWApprove(GetCompanyWaitYWApproveModel model)
        {
            string conn = Function.GetConnectionString();

            GetCompanyWaitYWApproveResultModel resultObj = new GetCompanyWaitYWApproveResultModel();

            try
            {
                if (model == null)
                {
                    throw new Exception("对象为空！");
                }

                if (string.IsNullOrEmpty(model.memberid))
                {
                    throw new Exception("用户标识不能为空！");
                }

                string memberid = model.memberid.Trim();

                string sql = $@"select FIN_BILLTB.comcd,
	                            CD_COMTB.comnm,
	                            count(*) as nums
                            from FIN_BILLTB
	                            inner join FIN_REVFLOW ON FIN_BILLTB.billid = FIN_REVFLOW.applyid
	                            inner join CD_COMTB on CD_COMTB.comcd = FIN_BILLTB.comcd
                            where (FIN_REVFLOW.revlb1 = 'N' and FIN_REVFLOW.reviewer1 = '{memberid}' 
                                        OR FIN_REVFLOW.revlb2 = 'N' and FIN_REVFLOW.reviewer2 = '{memberid}') 
                                and FIN_REVFLOW.applytp = 'BILL'
                            group by FIN_BILLTB.comcd,
	                            CD_COMTB.comnm
                            having count(*) > 0";

                //获取数据
                DataSet dsInfo = SqlHelper.GetDataSet(sql, conn);

                List<GetCompanyWaitYWApproveResultDataModel> lst = new List<GetCompanyWaitYWApproveResultDataModel>();

                foreach(DataRow row in dsInfo.Tables[0].Rows)
                {
                    GetCompanyWaitYWApproveResultDataModel data = new GetCompanyWaitYWApproveResultDataModel();

                    data.companycd = row["comcd"].ToString().Trim();
                    data.companyname = row["comnm"].ToString().Trim();
                    data.nums = row["nums"] == DBNull.Value ? 0 : Convert.ToInt32(row["nums"]);//.ToString().Trim();

                    lst.Add(data);
                }

                resultObj.code = "0";
                resultObj.message = "";
                resultObj.data = lst;
                return JsonConvert.SerializeObject(resultObj);
            }
            catch (Exception ex)
            {
                resultObj.code = "99";
                resultObj.message = ex.Message;
                resultObj.data = null;
                return JsonConvert.SerializeObject(resultObj);
            }
        }

        /// <summary>
        /// 取待审批的业务单
        /// </summary> 
        public string GetWaitYWApproveBill(GetWaitYWApproveBillModel model)
        {
            string conn = Function.GetConnectionString();

            GetWaitYWApproveBillResultModel resultObj = new GetWaitYWApproveBillResultModel();

            try
            {
                if (model == null)
                {
                    throw new Exception("对象为空！");
                }

                if (string.IsNullOrEmpty(model.memberid))
                {
                    throw new Exception("用户标识不能为空！");
                }

                if (string.IsNullOrEmpty(model.companycd))
                {
                    throw new Exception("公司代码不能为空！");
                }

                string memberid = model.memberid.Trim();
                string companycd = model.companycd.Trim();

                string sql = $@"select FIN_BILLTB.billid,
                                       FIN_BILLTB.amt_rec,     --收费金额
                                       FIN_BILLTB.amt_pay,     --付费金额
                                       FIN_BILLTB.amt_rec_ed,  --已收金额
                                       FIN_BILLTB.amt_pay_ed,  --已付金额
                                       FIN_BILLTB.remark,
                                       FIN_BILLTB.cusid,
                                       CRM_CUSTOMERTB.cusnm,
                                       SYS_USERTB.memberid as createid,
                                       SYS_USERTB.nickname as createname,
                                       FIN_REVFLOW.submitdt,
                                       CD_BASIC.speccn as km
                                    from FIN_BILLTB
                                       inner join FIN_REVFLOW ON FIN_BILLTB.billid = FIN_REVFLOW.applyid
                                       inner join CD_COMTB on CD_COMTB.comcd = FIN_BILLTB.comcd
                                       left outer join CRM_CUSTOMERTB on CRM_CUSTOMERTB.cusid = FIN_BILLTB.cusid
                                       left outer join SYS_USERTB on SYS_USERTB.memberid = FIN_BILLTB.createid
                                       left outer join CD_BASIC  on CD_BASIC.cd = FIN_BILLTB.billtp AND CD_BASIC.attrcd = 'GSKM'
                                    where (FIN_REVFLOW.revlb1 = 'N' OR FIN_REVFLOW.revlb2 = 'N')
                                        and(FIN_REVFLOW.reviewer1 = '{memberid}' or FIN_REVFLOW.reviewer2 = '{memberid}')
                                        and FIN_REVFLOW.applytp = 'BILL'
                                        and FIN_BILLTB.comcd = '{companycd}'";

                
                //获取数据
                DataSet dsInfo = SqlHelper.GetDataSet(sql, conn);

                List<GetWaitYWApproveBillResultDataModel> lst = new List<GetWaitYWApproveBillResultDataModel>();

                foreach (DataRow row in dsInfo.Tables[0].Rows)
                {
                    GetWaitYWApproveBillResultDataModel data = new GetWaitYWApproveBillResultDataModel();

                    data.billid = row["billid"].ToString().Trim();
                    data.cusname = row["cusnm"].ToString().Trim();
                    data.cusid = row["cusid"].ToString().Trim();


                    data.amt_rec = row["amt_rec"].ToString().Trim();
                    data.amt_pay = row["amt_pay"].ToString().Trim();
                    data.amt_rec_ed = row["amt_rec_ed"].ToString().Trim();
                    data.amt_pay_ed = row["amt_pay_ed"].ToString().Trim();

                    data.km = row["km"].ToString().Trim();

                    data.remark = row["remark"].ToString().Trim();

                    data.createid = row["createid"].ToString().Trim();
                    data.createname = row["createname"].ToString().Trim();

                    data.submitdt = row["submitdt"] == DBNull.Value ? "" : Convert.ToDateTime(row["submitdt"]).ToString("yyyy-MM-dd HH:mm");


                    lst.Add(data);
                }

                resultObj.code = "0";
                resultObj.message = "";
                resultObj.data = lst;
                return JsonConvert.SerializeObject(resultObj);
            }
            catch (Exception ex)
            {
                resultObj.code = "99";
                resultObj.message = ex.Message;
                resultObj.data = null;
                return JsonConvert.SerializeObject(resultObj);
            }
        }


        /// <summary>
        /// 取业务单明细
        /// </summary> 
        public string GetWaitYWApproveBillDetail(GetWaitYWApproveBillDetailModel model)
        {
            string conn = Function.GetConnectionString();

            GetWaitYWApproveBillDetailResultModel resultObj = new GetWaitYWApproveBillDetailResultModel();

            try
            {
                if (model == null)
                {
                    throw new Exception("对象为空！");
                }

                if (string.IsNullOrEmpty(model.billid))
                {
                    throw new Exception("billid不能为空！");
                }



                string billid = model.billid.Trim();

                string sql = $@"select BIZ_FEE_DETAIL.billid,
                                    BIZ_FEE_DETAIL.objtp as zdname,  --账单名称
                                    BIZ_FEE_DETAIL.amt_paytotal,    --本次应付
                                    BIZ_FEE_DETAIL.amt_rectotal,    --本次应收
                                    BIZ_PROJDETAIL.dtlnum,    --销售数量
                                    BIZ_FEE_DETAIL.num,         --本次数量
                                    BIZ_PROJDETAIL.amt_selprice,        --销售单价
                                    BIZ_FEE_DETAIL.amt_payprice,        --本次单价
                                    BIZ_FEE_DETAIL.model as xh,     --型号
                                    BIZ_FEE_DETAIL.objdesc    --说明
                                from BIZ_FEE_DETAIL 
                                    LEFT OUTER JOIN BIZ_PROJDETAIL ON dbo.BIZ_FEE_DETAIL.projdtlid = BIZ_PROJDETAIL.projdtlid

                                where BIZ_FEE_DETAIL.billid = {billid}  ";


                //获取数据
                DataSet dsInfo = SqlHelper.GetDataSet(sql, conn);

                List<GetWaitYWApproveBillDetailResultDataModel> lst = new List<GetWaitYWApproveBillDetailResultDataModel>();

                foreach (DataRow row in dsInfo.Tables[0].Rows)
                {
                    GetWaitYWApproveBillDetailResultDataModel data = new GetWaitYWApproveBillDetailResultDataModel();

                    data.billid = row["billid"].ToString().Trim();
                    data.zdname = row["zdname"].ToString().Trim();
                    data.amt_paytotal = row["amt_paytotal"].ToString().Trim();
                    data.amt_rectotal = row["amt_rectotal"].ToString().Trim();
                    data.dtlnum = row["dtlnum"].ToString().Trim();
                    data.num = row["num"].ToString().Trim();
                    data.amt_selprice = row["amt_selprice"].ToString().Trim();
                    data.amt_payprice = row["amt_payprice"].ToString().Trim();
                    data.xh = row["xh"].ToString().Trim();
                    data.objdesc = row["objdesc"].ToString().Trim();


                    lst.Add(data);
                }

                //取文件数
                sql = $@"select * from SYS_ARCHIVETB where arckey = {billid} and arcrule = 'BIZBILL'";
                DataSet dsFiles = SqlHelper.GetDataSet(sql, conn);

                resultObj.files = dsFiles.Tables[0].Rows.Count.ToString();

                resultObj.code = "0";
                resultObj.message = "";
                resultObj.data = lst;
                return JsonConvert.SerializeObject(resultObj);
            }
            catch (Exception ex)
            {
                resultObj.code = "99";
                resultObj.message = ex.Message;
                resultObj.data = null;
                return JsonConvert.SerializeObject(resultObj);
            }
        } 


        /// <summary>
        /// 获取审批文件列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public string FindApproveFiles(FindApproveFileModel model)
        {
            string conn = Function.GetConnectionString();

            FindApproveFileResultModel resultObj = new FindApproveFileResultModel();

            try
            {
                if (model == null)
                {
                    throw new Exception("对象为空！");
                }

                if (string.IsNullOrEmpty(model.memberid))
                {
                    throw new Exception("用户标识不能为空！");
                }

                if (string.IsNullOrEmpty(model.billid))
                {
                    throw new Exception("billid不能为空！");
                }

                string memberid = model.memberid.Trim();
                string billid = model.billid.Trim();
                 

                string sql = $@"select * from SYS_ARCHIVETB where arckey = {billid} and arcrule = 'BIZBILL'";

                //获取数据
                DataSet dsInfo = SqlHelper.GetDataSet(sql, conn);

                List<FindApproveFileResultDataModel> lst = new List<FindApproveFileResultDataModel>();


                string fileurl = System.Web.Configuration.WebConfigurationManager.AppSettings["FileUrl"];

                foreach (DataRow row in dsInfo.Tables[0].Rows)
                {
                    string extnm = row["extnm"].ToString().Trim().Replace(".", "").ToLower();

                    FindApproveFileResultDataModel data = new FindApproveFileResultDataModel();
                    data.realnm = row["filenm"].ToString().Trim();
                    data.filename = fileurl + @"/ywimage/" + row["arcpath"].ToString().Trim().Replace(@"\",@"/") + @"/" + row["filerealnm"].ToString().Trim();

                    if (extnm == "jpg" || extnm == "bmp" || extnm == "gif" || extnm == "png")
                    {
                        //图片
                        data.filetp = "img";
                    }
                    else
                    {
                        data.filetp = "doc";
                    }

                    lst.Add(data);
                }
                 

                resultObj.code = "0";
                resultObj.message = "";
                resultObj.data = lst;
                return JsonConvert.SerializeObject(resultObj);
            }
            catch (Exception ex)
            {
                resultObj.code = "99";
                resultObj.message = ex.Message;
                resultObj.data = null;
                return JsonConvert.SerializeObject(resultObj);
            }
        }
    }
}
